import express, { Response, Request, Application } from 'express'
import { Connection, createConnection } from 'mysql'
// import cors from 'cors'
// @ts-ignore
import bodyPaser from 'body-parser'

import st from './self_modules/sql-tool'

const app: Application = express()
const sql: Connection = createConnection({
    // user: 数据库用户名,
    // password: 用户名对应密码,
    // host: 数据库服务器所在地址，本机填‘localhost’,
    // port: 数据库服务端口，默认3306，可忽略不填
    // database: 数据库名
    user: 'Lain',
    password: 'allLoveLain.2001',
    host: '101.33.244.82',
    database: 'harbin_park',
})
sql.connect(err => {
    if (err) {
        console.log('mysql connect faild')
    } else {
        console.log('mysql connect success')
    }
})

const PORT = process.env.PORT || 8102

// app.use(cors())
app.use(bodyPaser.urlencoded({ extended: false }))
app.use(bodyPaser.json())
app.use('/static/', express.static('./src/'))

// sql processing model start
// get sql select results | used to get any data list
app.post("/sql-select", async (req: Request, res: Response): Promise<void> => {
    var body = req.body
    var sqlObj = new st.SQLObj
    try {
        sqlObj.query = 'select'
        sqlObj.table = body['table']
        for (var idx = 0; idx < body['keys'].length; idx += 1) {
            sqlObj.data.set(body['keys'][idx], {type: body['key_types'][idx], value: body['keys'][idx]})
        }
        if (body['where'] && body['where_key']) {
            sqlObj.where.type = body['where_key']
            sqlObj.where.condition = body['where']
        }
        if (body['group_by_keys']) {
            sqlObj.options.group_by = body['group_by_keys']
        }
        if (body['order_by_keys']) {
            sqlObj.options.order_by = body['order_by_keys']
        }
        if (body['limit']) {
            sqlObj.options.limit = body['limit']
        }

        let results = await st.ControlAPI_obj_async(sql, sqlObj)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true, data: results }))
        res.end()
    } catch (error) {
        console.error(error)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

// used to delete object
app.delete("/sql-deal", (req: Request, res: Response): void => {
    var query = req.query
    /*
    * table: target table
    * id: target id for delete
    */

    var sqlObj = new st.SQLObj
    try {
        sqlObj.query = 'delete'
        // @ts-ignore
        sqlObj.table = query['table']
        sqlObj.where.condition.push(`id=${query['id']}`)

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

// used to add object
app.post("/sql-deal",  (req: Request, res: Response): void => {
    var body = req.body

    /*
    * query
    *   table: target table
    * body
    *   keys: keys list for object addition
    *   values: values list for object addition
    */

    var sqlObj = new st.SQLObj
    try {
        sqlObj.query = body['method']
        sqlObj.table = body['table']
        for (var idx = 0; idx < body['keys'].length; idx += 1) {
            sqlObj.data.set(body['keys'][idx], {type: body['key_types'][idx], value: body['values'][idx]})
        }

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

// used to edit object
app.put("/sql-deal", (req: Request, res: Response): void => {
    var body = req.body
    // var query = req.query

    /*
    * query
    *   table: target table
    *   id: target id
    * body
    *   keys: keys list for object addition
    *   values: values list for object addition
    */

    var sqlObj = new st.SQLObj
    try {
        sqlObj.query = 'update'
        // @ts-ignore
        sqlObj.table = body['table']
        for (var idx = 0; idx < body['keys'].length; idx += 1) {
            sqlObj.data.set(body['keys'][idx], {type: body['key_types'][idx], value: body['values'][idx]})
        }
        sqlObj.where.condition = body['where']

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

// used to get single object
app.get("/sql-deal",  async (req: Request, res: Response): Promise<void> => {
    var query = req.query

    var sqlObj = new st.SQLObj
    try {
        sqlObj.query = 'select'
        // @ts-ignore
        sqlObj.table = query['table']
        sqlObj.where.condition.push(`id=${query['id']}`)

        let results = await st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" }) 
        res.write(JSON.stringify({ ok: true, data: results }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})
// sql processing model end

// auth model start
app.post("/login", (req: Request, res: Response): void => {
    var body = req.body

    res.writeHead(200, { "Content-Type": "application/json" })
    res.write(JSON.stringify({ ok: true }))
    res.end()
})
// auth model end

// admin manage model start
// lock admin
app.get("/admins/lock", (req: Request, res: Response): void => {
    var query = req.query

    var id = query['id']
    try {
        var sqlObj = new st.SQLObj()
        sqlObj.query = 'update'
        sqlObj.table = 'Administrator'
        sqlObj.data.set("restricted", {type: 'number', value: 0})
        sqlObj.where.type = 'and'
        sqlObj.where.condition = [`id=${id}`]

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

app.delete("/admins/lock", (req: Request, res: Response): void => {
    var query = req.query

    var id = query['id']
    try {
        var sqlObj = new st.SQLObj()
        sqlObj.query = 'update'
        sqlObj.table = 'Administrator'
        sqlObj.data.set("restricted", {type: 'number', value: 1})
        sqlObj.where.type = 'and'
        sqlObj.where.condition = [`id=${id}`]

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})
// admin manage model end

// base users lock or unlock model start
// lock
app.get("/base-users/lock", (req: Request, res: Response): void => {
    var query = req.query

    var id = query['id']
    var table= query['table']
    try {
        var sqlObj = new st.SQLObj()
        sqlObj.query = 'update'
        sqlObj.table = table as string
        sqlObj.data.set("restricted", {type: 'number', value: 0})
        sqlObj.where.type = 'and'
        sqlObj.where.condition = [`id=${id}`]

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})

// unlock
app.delete("/base-users/lock", (req: Request, res: Response): void => {
    var query = req.query

    var id = query['id']
    var table = query['table']
    try {
        var sqlObj = new st.SQLObj()
        sqlObj.query = 'update'
        sqlObj.table = table as string
        sqlObj.data.set("restricted", {type: 'number', value: 1})
        sqlObj.where.type = 'and'
        sqlObj.where.condition = [`id=${id}`]

        st.ControlAPI_obj(sql, sqlObj, () => { })

        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: true }))
        res.end()
    } catch (err) {
        console.error(err)
        res.writeHead(200, { "Content-Type": "application/json" })
        res.write(JSON.stringify({ ok: false }))
        res.end()
    }
})
// base users lock or unlock model end

app.listen(PORT, (): void => {
    console.log('backend server now start!')
})